using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using WegasManagementSystem.Models;

namespace WegasManagementSystem.DAL
{
    /// <summary>
    /// 用户数据访问类
    /// </summary>
    public class UserDAL
    {
        /// <summary>
        /// 验证用户登录
        /// </summary>
        /// <param name="username">用户名</param>
        /// <param name="password">密码</param>
        /// <returns>登录成功返回用户对象，失败返回null</returns>
        public User ValidateUser(string username, string password)
        {
            string sql = "SELECT * FROM sys_user WHERE username = @username AND password = @password AND is_active = 1";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@username", username),
                new MySqlParameter("@password", password)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                return MapRowToUser(dt.Rows[0]);
            }
            return null;
        }

        /// <summary>
        /// 添加新用户
        /// </summary>
        /// <param name="user">用户对象</param>
        /// <returns>新用户ID，失败返回0</returns>
        public int AddUser(User user)
        {
            string sql = @"INSERT INTO sys_user (username, password, user_role, emp_id, is_active, created_time, updated_time) 
                          VALUES (@username, @password, @user_role, @emp_id, @is_active, @created_time, @updated_time);
                          SELECT LAST_INSERT_ID();";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@username", user.Username),
                new MySqlParameter("@password", Utils.SecurityHelper.EncryptPassword(user.Password)),
                new MySqlParameter("@user_role", user.UserRole),
                new MySqlParameter("@emp_id", user.EmpId.HasValue ? (object)user.EmpId.Value : DBNull.Value),
                new MySqlParameter("@is_active", user.IsActive),
                new MySqlParameter("@created_time", user.CreatedTime),
                new MySqlParameter("@updated_time", user.UpdatedTime)
            };

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters);
            return result != null ? Convert.ToInt32(result) : 0;
        }

        /// <summary>
        /// 更新用户信息
        /// </summary>
        /// <param name="user">用户对象</param>
        /// <returns>更新成功返回true，失败返回false</returns>
        public bool UpdateUser(User user)
        {
            string sql = @"UPDATE sys_user 
                          SET username = @username, user_role = @user_role, 
                              emp_id = @emp_id, is_active = @is_active, updated_time = @updated_time
                          WHERE user_id = @user_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@user_id", user.UserId),
                new MySqlParameter("@username", user.Username),
                new MySqlParameter("@user_role", user.UserRole),
                new MySqlParameter("@emp_id", user.EmpId.HasValue ? (object)user.EmpId.Value : DBNull.Value),
                new MySqlParameter("@is_active", user.IsActive),
                new MySqlParameter("@updated_time", DateTime.Now)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 修改用户密码
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <param name="newPassword">新密码</param>
        /// <returns>修改成功返回true，失败返回false</returns>
        public bool ChangePassword(int userId, string newPassword)
        {
            string sql = "UPDATE sys_user SET password = @password, updated_time = @updated_time WHERE user_id = @user_id";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@user_id", userId),
                new MySqlParameter("@password", Utils.SecurityHelper.EncryptPassword(newPassword)),
                new MySqlParameter("@updated_time", DateTime.Now)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <returns>删除成功返回true，失败返回false</returns>
        public bool DeleteUser(int userId)
        {
            string sql = "DELETE FROM sys_user WHERE user_id = @user_id";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@user_id", userId)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 获取所有用户
        /// </summary>
        /// <returns>用户列表</returns>
        public List<User> GetAllUsers()
        {
            string sql = @"SELECT u.*, e.name as emp_name 
                          FROM sys_user u 
                          LEFT JOIN emp_employee e ON u.emp_id = e.emp_id";

            DataTable dt = DBConnection.ExecuteDataTable(sql);
            List<User> users = new List<User>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    users.Add(MapRowToUser(row));
                }
            }

            return users;
        }

        /// <summary>
        /// 根据ID获取用户
        /// </summary>
        /// <param name="userId">用户ID</param>
        /// <returns>用户对象</returns>
        public User GetUserById(int userId)
        {
            string sql = @"SELECT u.*, e.name as emp_name 
                          FROM sys_user u 
                          LEFT JOIN emp_employee e ON u.emp_id = e.emp_id 
                          WHERE u.user_id = @user_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@user_id", userId)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                return MapRowToUser(dt.Rows[0]);
            }
            return null;
        }

        /// <summary>
        /// 检查用户名是否存在
        /// </summary>
        /// <param name="username">用户名</param>
        /// <returns>存在返回true，不存在返回false</returns>
        public bool CheckUsernameExists(string username)
        {
            string sql = "SELECT COUNT(*) FROM sys_user WHERE username = @username";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@username", username)
            };

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters);
            return result != null && Convert.ToInt32(result) > 0;
        }

        /// <summary>
        /// 将DataRow映射到User对象
        /// </summary>
        /// <param name="row">DataRow</param>
        /// <returns>User对象</returns>
        private User MapRowToUser(DataRow row)
        {
            User user = new User();
            user.UserId = Convert.ToInt32(row["user_id"]);
            user.Username = row["username"].ToString();
            user.Password = row["password"].ToString();
            user.UserRole = row["user_role"].ToString();
            
            if (row["emp_id"] != DBNull.Value)
                user.EmpId = Convert.ToInt32(row["emp_id"]);
            
            user.IsActive = Convert.ToBoolean(row["is_active"]);
            user.CreatedTime = Convert.ToDateTime(row["created_time"]);
            user.UpdatedTime = Convert.ToDateTime(row["updated_time"]);

            // 可能是连接查询的额外字段
            if (row.Table.Columns.Contains("emp_name") && row["emp_name"] != DBNull.Value)
                user.EmpName = row["emp_name"].ToString();

            return user;
        }
    }
} 